using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using TienTeTuong;
using log4net;

namespace TienTeTuong
{
    /// <summary>
    /// Data access layer class for Product
    /// </summary>
    class CategoryDetailDAO
    {


        public List<CategoryDetailDTO> GetListCategoryDetailByCateID(string CateID)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;

            var lstCategory = new List<CategoryDetailDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"SELECT * FROM categorydetail Where CateID='" + CateID + "' ORDER BY CreatedDate";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new CategoryDetailDTO();

                        cate.CateDetailID = reader[0].ToString();
                        cate.Name = reader[1].ToString();
                        cate.CateID = reader[2].ToString();
                        cate.Description = reader[3].ToString();
                        cate.VipLevel = int.Parse(reader[4].ToString());
                        cate.Active = reader[5].ToString() == "1";
                        cate.Image = reader[6].ToString();
                        cate.Price = Convert.ToDouble(reader[7].ToString());
                        cate.Status = Convert.ToInt32(reader[8].ToString());
                        cate.Quantity = Convert.ToDouble(reader[9].ToString());
                        cate.QuantitySold = Convert.ToDouble(reader[10].ToString());
                        cate.CreatedDate = Convert.ToDateTime(reader[11].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[12].ToString());

                        lstCategory.Add(cate);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstCategory;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstCategory;
            }
        }

        public CategoryDetailDTO GetCategoryDetailByID(string CateDetailID)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var cate = new CategoryDetailDTO();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"SELECT * FROM categorydetail Where CateDetailID='" + CateDetailID + "'";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        // var cate = new CategoryDetailDTO();

                        cate.CateDetailID = reader[0].ToString();
                        cate.Name = reader[1].ToString();
                        cate.CateID = reader[2].ToString();
                        cate.Description = reader[3].ToString();
                        cate.VipLevel = int.Parse(reader[4].ToString());
                        cate.Active = reader[5].ToString() == "1";
                        cate.Image = reader[6].ToString();
                        cate.Price = Convert.ToDouble(reader[7].ToString());
                        cate.Status = Convert.ToInt32(reader[8].ToString());
                        cate.Quantity = Convert.ToDouble(reader[9].ToString());
                        cate.QuantitySold = Convert.ToDouble(reader[10].ToString());
                        cate.CreatedDate = Convert.ToDateTime(reader[11].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[12].ToString());
                        cate.Hot = reader[13].ToString() == "1";
                        cate.Image2 = reader[14].ToString();
                        cate.Image3 = reader[15].ToString();
                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                        throw;
                    }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return cate;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return cate;
            }
        }
        /// <summary>
        /// Get list category detail with paging
        /// </summary>
        /// <param name="CateID"></param>
        /// <returns></returns>
        public List<CategoryDetailDTO> GetListCategoryByPage(string CateID, int offsetFirstRow, int numPerpage)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstCategory = new List<CategoryDetailDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"SELECT * FROM categorydetail Where CateID='" + CateID + "' ORDER BY CreatedDate LIMIT " + offsetFirstRow + " , " + numPerpage;
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new CategoryDetailDTO();

                        cate.CateDetailID = reader[0].ToString();
                        cate.Name = reader[1].ToString();
                        cate.CateID = reader[2].ToString();
                        cate.Description = reader[3].ToString();
                        cate.VipLevel = int.Parse(reader[4].ToString());
                        cate.Active = reader[5].ToString() == "1";
                        cate.Image = reader[6].ToString();
                        cate.Price = Convert.ToDouble(reader[7].ToString());
                        cate.Status = Convert.ToInt32(reader[8].ToString());
                        cate.Quantity = Convert.ToDouble(reader[9].ToString());
                        cate.QuantitySold = Convert.ToDouble(reader[10].ToString());
                        cate.CreatedDate = Convert.ToDateTime(reader[11].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[12].ToString());

                        lstCategory.Add(cate);

                    }
                    catch (Exception)
                    {

                    }
                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstCategory;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstCategory;
            }
        }

        /// <summary>
        /// Get list category detail with paging
        /// </summary>
        /// <param name="CateID"></param>
        /// <returns></returns>
        public List<CategoryDetailDTO> SearchListCategoryByPage(string CateID, string keyword, int offsetFirstRow, int numPerpage)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstCategory = new List<CategoryDetailDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"SELECT * FROM categorydetail Where CateID='" + CateID + "'" + " AND Name LIKE '%" + keyword + "%' ORDER BY CreatedDate LIMIT " + offsetFirstRow + " , " + numPerpage;
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new CategoryDetailDTO();

                        cate.CateDetailID = reader[0].ToString();
                        cate.Name = reader[1].ToString();
                        cate.CateID = reader[2].ToString();
                        cate.Description = reader[3].ToString();
                        cate.VipLevel = int.Parse(reader[4].ToString());
                        cate.Active = reader[5].ToString() == "1";
                        cate.Image = reader[6].ToString();
                        cate.Price = Convert.ToDouble(reader[7].ToString());
                        cate.Status = Convert.ToInt32(reader[8].ToString());
                        cate.Quantity = Convert.ToDouble(reader[9].ToString());
                        cate.QuantitySold = Convert.ToDouble(reader[10].ToString());
                        cate.CreatedDate = Convert.ToDateTime(reader[11].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[12].ToString());

                        lstCategory.Add(cate);

                    }
                    catch (Exception)
                    {

                    }
                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstCategory;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstCategory;
            }
        }
        /// <summary>
        /// Get List Hot Category Detail
        /// </summary>
        /// <returns></returns>
        public List<CategoryDetailDTO> GetHotListCategoryDetail()
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstCategory = new List<CategoryDetailDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"SELECT * FROM categorydetail Where Hot=1";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new CategoryDetailDTO();

                        cate.CateDetailID = reader[0].ToString();
                        cate.Name = reader[1].ToString();
                        cate.CateID = reader[2].ToString();
                        cate.Description = reader[3].ToString();
                        cate.VipLevel = int.Parse(reader[4].ToString());
                        cate.Active = reader[5].ToString() == "1";
                        cate.Image = reader[6].ToString();
                        cate.Price = Convert.ToDouble(reader[7].ToString());
                        cate.Status = Convert.ToInt32(reader[8].ToString());
                        cate.Quantity = Convert.ToDouble(reader[9].ToString());
                        cate.QuantitySold = Convert.ToDouble(reader[10].ToString());
                        cate.CreatedDate = Convert.ToDateTime(reader[11].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[12].ToString());
                        cate.Hot = reader[13].ToString() == "1";

                        lstCategory.Add(cate);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstCategory;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstCategory;
            }
        }
        public List<CategoryDetailDTO> GetHotListCategoryDetail(int Top)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstCategory = new List<CategoryDetailDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"SELECT * FROM categorydetail Where Hot=1 LIMIT " + Top;
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new CategoryDetailDTO();

                        cate.CateDetailID = reader[0].ToString();
                        cate.Name = reader[1].ToString();
                        cate.CateID = reader[2].ToString();
                        cate.Description = reader[3].ToString();
                        cate.VipLevel = int.Parse(reader[4].ToString());
                        cate.Active = reader[5].ToString() == "1";
                        cate.Image = reader[6].ToString();
                        cate.Price = Convert.ToDouble(reader[7].ToString());
                        cate.Status = Convert.ToInt32(reader[8].ToString());
                        cate.Quantity = Convert.ToDouble(reader[9].ToString());
                        cate.QuantitySold = Convert.ToDouble(reader[10].ToString());
                        cate.CreatedDate = Convert.ToDateTime(reader[11].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[12].ToString());
                        cate.Hot = reader[13].ToString() == "1";
                        cate.Image2 = reader[14].ToString();
                        cate.Image3 = reader[15].ToString();

                        lstCategory.Add(cate);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstCategory;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstCategory;
            }
        }
        /// <summary>
        /// Dem so tong so luong category Detail dua tren CateID
        /// </summary>
        /// <param name="CateID"></param>
        /// <returns></returns>
        public int GetNumCategoryDetail(string CateID)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            int total = 0;
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"SELECT Count(*) FROM categorydetail Where CateID='" + CateID + "'";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    total = int.Parse(reader[0].ToString());
                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return total;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return total;
            }
        }

        /// <summary>
        /// Dem so tong so luong category Detail dua tren CateID and keyword
        /// </summary>
        /// <param name="CateID"></param>
        /// <returns></returns>
        public int SearchNumCategoryDetail(string CateID, string keyword)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            int total = 0;
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"SELECT Count(*) FROM categorydetail Where CateID='" + CateID + "'" + " AND Name LIKE '%" + keyword + "%'";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    total = int.Parse(reader[0].ToString());
                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return total;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return total;
            }
        }

        /// <summary>
        /// Get List Category Detail
        /// </summary>
        /// <returns></returns>
        public List<CategoryDetailDTO> GetListCategoryDetail()
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstCategory = new List<CategoryDetailDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"SELECT * FROM categorydetail";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new CategoryDetailDTO();

                        cate.CateDetailID = reader[0].ToString();
                        cate.Name = reader[1].ToString();
                        cate.CateID = reader[2].ToString();
                        cate.Description = reader[3].ToString();
                        cate.VipLevel = int.Parse(reader[4].ToString());
                        cate.Active = reader[5].ToString() == "1";
                        cate.Image = reader[6].ToString();
                        cate.Price = Convert.ToDouble(reader[7].ToString());
                        cate.Status = Convert.ToInt32(reader[8].ToString());
                        cate.Quantity = Convert.ToDouble(reader[9].ToString());
                        cate.QuantitySold = Convert.ToDouble(reader[10].ToString());
                        cate.CreatedDate = Convert.ToDateTime(reader[11].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[12].ToString());
                        cate.Hot = reader[13].ToString() == "1";
                        cate.Image2 = reader[14].ToString();
                        cate.Image3 = reader[15].ToString();

                        lstCategory.Add(cate);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstCategory;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstCategory;
            }
        }

        public bool delete(string key)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            int reader = 0;
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                var strSQL = @"Delete FROM `tientetuong`.`categorydetail` where `categorydetail`.`CateDetailID` = '" + key + "'";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }


        /// <summary>
        /// Search Category Detail
        /// </summary>
        /// <returns></returns>
        public List<CategoryDetailDTO> searchCategoryDetailByName(string key)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstCategory = new List<CategoryDetailDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"SELECT * FROM categorydetail where categorydetail.Name Like '%" + key + "%' Order by categorydetail.Name asc";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new CategoryDetailDTO();

                        cate.CateDetailID = reader[0].ToString();
                        cate.Name = reader[1].ToString();
                        cate.CateID = reader[2].ToString();
                        cate.Description = reader[3].ToString();
                        cate.VipLevel = int.Parse(reader[4].ToString());
                        cate.Active = reader[5].ToString() == "1";
                        cate.Image = reader[6].ToString();
                        cate.Price = Convert.ToDouble(reader[7].ToString());
                        cate.Status = Convert.ToInt32(reader[8].ToString());
                        cate.Quantity = Convert.ToDouble(reader[9].ToString());
                        cate.QuantitySold = Convert.ToDouble(reader[10].ToString());
                        cate.CreatedDate = Convert.ToDateTime(reader[11].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[12].ToString());
                        cate.Hot = reader[13].ToString() == "1";
                        cate.Image2 = reader[14].ToString();
                        cate.Image3 = reader[15].ToString();
                        lstCategory.Add(cate);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstCategory;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstCategory;
            }
        }
        public bool update(CategoryDetailDTO item)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            ProductDetailBUS proBus = new ProductDetailBUS();
            int reader = 0;
            try
            {
                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                var strSQL = @"UPDATE `categorydetail` SET  `Name` =  '" + item.Name + "', "
               + "`Description` =  '" + item.Description + "', "
                    + "`CateID` =  '" + item.CateID + "', "
               + "`Image` =  '" + item.Image + "', "
               + "`Image1` =  '" + item.Image2 + "', "
               + "`Image2` =  '" + item.Image3 + "', "
               + "`Price` =  '" + item.Price.ToString() + "',"
               + "`Hot` =  b'" + (item.Hot ? "1" : "0") + "', "
               + "`Active` =  b'" + (bool.Parse(item.Active.ToString()) ? "1" : "0") + "'"
               + " WHERE  `categorydetail`.`CateDetailID` =  '" + item.CateDetailID + "';";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }

        public bool add(CategoryDetailDTO item)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            ProductDetailBUS proBus = new ProductDetailBUS();
            int reader = 0;
            try
            {
                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                var strSQL = @"Insert into `categorydetail` (`CateDetailID`, `Name`, `CateID`, `Description`, `VipLevel`, `Active`, `Image`, `Price`, `Status`, `Quantity`, `QuantitySold`, `Hot`, `Image1`, `Image2`)"
                    + " Values ('" + item.CateDetailID + "', '" + item.Name + "', '" + item.CateID + "', '" + item.Description + "', '" + item.VipLevel + "', b'" + ((bool)item.Active ? "1":"0") + "', '" + item.Image
                    + "', '" + item.Price + "', '" + item.Status + "', '" + item.Quantity + "', '" + item.QuantitySold + "', b'" + (item.Hot ? "1":"0") + "', '" + item.Image2 + "', '" + item.Image3 + "');";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }
    }
}
